<?php

class Elective_StudentController extends Zend_Controller_Action
{
    protected $_year;
    protected $_semester;

    protected function _init()
    {
        // 目前的學年學期
        $this->_year = $this->view->Semester['year'];
        $this->_semester = $this->view->Semester['semester'];
    }

    public function indexAction()
    {

    }

    /**
     *  以座號輸入新增學生
     */
    public function appendByNumberAction()
    {
        $year = $this->view->Semester['year'];
        $semester = $this->view->Semester['semester'];
        $gradeSn = (int) $this->getRequest()->getPost('gradeSn');
        $subjectSn = (int) $this->getRequest()->getPost('subjectSn');
        $numbers = explode(",", $this->getRequest()->getPost('id'));
       $studentSnArr =array();
        $errorStudent = '';
        foreach ($numbers as $classNumber)
        {
            $classSn = (int) substr($classNumber,0,-2);
            $number = (int) substr($classNumber,-2);

            // 查詢學生
            $q = Doctrine_Query::create()
            ->select('a.sn, a.name')
            ->from('Student_Model_Student a')
            ->leftJoin('a.Student_Model_StudentSemester b')
            ->where('b.year=? AND b.semester=? AND b.grade=? AND b.class_sn=? AND b.number=?',
            array($year, $semester, $gradeSn, $classSn, $number));
            $res  = $q->fetchOne();
            if ($res)
            $studentSnArr[$res->sn] = $res->name;
        }
        $groupArr = array();
        $q = Doctrine_Query::create()
        ->select('a.sn')
        ->from('Student_Model_StudentScoreElectiveGroup a')
        ->where('a.subject_sn IN (SELECT b.subject_sn FROM Student_Model_StudentScoreElectiveGroup b WHERE b.sn=?)'
        , $subjectSn);
        $res = $q->fetchArray();
        foreach($res as $row)
        $groupArr[] = $row['sn'];

        foreach ($studentSnArr as $studentSn=>$studentName) {
            $q = Doctrine_Query::create()
            ->select('student_sn')
            ->from('Student_Model_StudentScoreElectiveStudent')
            ->whereIn('group_sn',$groupArr)
            ->andWhere('student_sn=?', $studentSn);
            $res = $q->fetchOne();
            //如果已設定則放入錯誤陣列
            if ($res)
            $errorStudent = $studentName.' ';
            else {

                //查詢目前最後一個座號
                $max = Doctrine_Query::create()
                ->select('MAX(number) AS mm')
                ->from('Student_Model_StudentScoreElectiveStudent')
                ->where('group_sn=?', $subjectSn)->fetchOne()->mm;

                $StudentScoreElectiveStudent = new Student_Model_StudentScoreElectiveStudent();
                $StudentScoreElectiveStudent->group_sn = $subjectSn;
                $StudentScoreElectiveStudent->number = $max+1;
                $StudentScoreElectiveStudent->student_sn = $studentSn;
                $StudentScoreElectiveStudent->save();
            }
        }
        $this->_helper->json($errorStudent);
    }

    public function appendStudentAction()
    {

        $groupSn = (int)$this->getRequest()->getParam('group_sn');
        $studentArr = $this->getRequest()->getParam('studentSn');
        if (count($studentArr) == 0)
        $this->_helper->json(array('success'=>true));

        //查詢目前最後一個座號
        $max = Doctrine_Query::create()
        ->select('MAX(number) AS mm')
        ->from('Student_Model_StudentScoreElectiveStudent')
        ->where('group_sn=?', $groupSn)->fetchOne()->mm;

        foreach ($studentArr as $studentSn) {
            $max++;
            $electiveStudent = new Student_Model_StudentScoreElectiveStudent();
            $electiveStudent->group_sn = $groupSn;
            $electiveStudent->student_sn = $studentSn;
            $electiveStudent->number = $max;
            $electiveStudent->save();
        }
        $this->_helper->json(array('success'=>true));
    }

    /**
     * 選取分組學生
     * @return unknown_type
     */
    public function getStudentFromClassAction()
    {
        $this->getHelper('layout')->disableLayout();
        $this->_init();
        $classId = explode("-",$this->getRequest()->getParam('class_id'));
        $grade = (int)$classId[0];
        $classSn = (int)$classId[1];
        $student = new Student_Model_Student();
        $data = $student->getClassStudent($this->_year,$this->_semester,$grade,$classSn);
        $groupSn = (int)$this->getRequest()->getParam('group_sn');
        $studentSnArr = array_keys($data);
        // 找同一組別的科目及最多成員數
        $q = Doctrine_Query::create()
        ->select('subject_sn,member')
        ->from('Student_Model_StudentScoreElectiveGroup a')
        ->Where('a.sn=?', $groupSn)->fetchOne();

        $subjectSn = $q->subject_sn;
        $member = $q->member;

        // 找出已編排的人數
        $currentMember = Doctrine_Query::create()
        ->select('COUNT(*) AS cc')
        ->from('Student_Model_StudentScoreElectiveStudent')
        ->where('group_sn=?', $groupSn)
        ->fetchOne()->cc;

        $q = Doctrine_Query::create()
        ->select('a.sn, a.student_sn')
        ->from('Student_Model_StudentScoreElectiveStudent a')
        ->where('group_sn IN (SELECT b.sn FROM Student_Model_StudentScoreElectiveGroup b
         WHERE b.subject_sn=?)',$subjectSn);

        $res = $q->fetchArray();

        // 篩選所選班級已加入 分組人員
        foreach($res as $row) {
            if (in_array($row['student_sn'], $studentSnArr)) {
                $data[$row['student_sn']]['selSn'] = $row['sn'];
            }
        }
        $this->view->currentMember = $currentMember;
        $this->view->member = $member;
        $this->view->group_sn = $groupSn;
        $this->view->data = $data;
    }

    public function getNewStudentAction()
    {
        $this->getHelper('layout')->disableLayout();
        $grade = (int)$this->getRequest()->getPAram('grade');
        $group_sn = (int)$this->getRequest()->getParam('group_sn');
        $this->_init();
        //$q = Doctrine_Query::create()

        $this->view->selClass =  $this->_year.'-'.$this->_semester.'-'.$grade;

    }

    public function deleteAction()
    {
        $sn = (int)$this->getRequest()->getParam('sn');
        $t = Doctrine_core::getTable('Student_Model_StudentScoreElectiveStudent');
        $t->findBy('sn',$sn)->delete();
        $this->_helper->json(true);
    }

    public function sortNumberAction()
    {
        $this->getHelper('layout')->disableLayout();
        $group_sn = (int) $this->getRequest()->getParam('group_sn');
        $numbers = $this->getRequest()->getParam('numbers');
        $t = Doctrine_core::getTable('Student_Model_StudentScoreElectiveStudent');
        foreach ($numbers as $studentSn => $number) {
            $s = $t->findBySql('group_sn=? AND student_sn=?',
            array($group_sn, $studentSn))->getFirst();
            if ($s) {
                $s->number = $number;
                $s->save();
            }
        }
        $this->_helper->json(true);
    }

    public function getSubjectAction()
    {
        $this->getHelper('layout')->disableLayout();
        $this->_init();
        $grade = (int) $this->getRequest()->getParam('grade');

        $q = Doctrine_Query::create()
        ->select('a.sn, a.group_name ,b.subject_sn AS subject_sn, c.name AS name, d.name AS teacher_name')
        ->from('Student_Model_StudentScoreElectiveGroup a')
        ->leftJoin('a.Semester_Model_SemesterTeacherCourse b')
        ->leftJoin('b.Semester_Model_SemesterCourse c')
        ->leftJoin('Teacher_Model_TeacherView d')
        ->where('b.year=?', $this->_year)
        ->andWhere('b.semester=?', $this->_semester)
        ->andWhere('b.grade=?', $grade)
        ->andWhere('b.teacher_sn=d.sn');
        //->groupBy()
        $res = $q->fetchArray();
        $arr = array();
        foreach($res as $row)
        $arr[$row['name']][$row['sn']] = $row['group_name'].' - '. $row['teacher_name'];


        $this->view->data = $arr;
    }

    public function getStudentsAction()
    {
        $this->_init();
        $this->getHelper('layout')->disableLayout();
        $sn = (int) $this->getRequest()->getParam('sn');
        $q = Doctrine_Query::create()
        ->select('a.*, b.sn AS student_sn, b.name AS name, b.sex AS sex,
         c.student_id AS student_id, c.number AS student_number, c.class_sn AS class_sn')
        ->from('Student_Model_StudentScoreElectiveStudent a')
        ->leftJoin('Student_Model_Student b')
        ->leftJoin('Student_Model_StudentSemester c')
        ->where('a.student_sn = b.sn')
        ->andWhere('b.sn=c.student_sn')
        ->andWHere('c.year=?', $this->_year)
        ->andWhere('c.semester=?', $this->_semester)
        ->andWHere('a.group_sn=?', $sn)
        ->orderBy('a.number, c.grade, c.class_sn, c.number');
        $this->view->group_sn = $sn;
        $this->view->data = $q->fetchArray();
    }

}

